from decimal import Decimal
from random import choice
from docx import Document
from docx.enum.style import WD_STYLE_TYPE
from docx.enum.table import WD_TABLE_ALIGNMENT
from docx.enum.table import WD_CELL_VERTICAL_ALIGNMENT
from docx.shared import Cm
from openpyxl import load_workbook
from docx.shared import Pt
from docx.oxml.ns import qn
from docx.shared import Cm, Inches, Pt  # 导入单位换算函数
from docx.enum.section import WD_ORIENTATION
import re


def xlsx2docx2(fn):
    wb = load_workbook(fn, data_only=False)
    document = Document()
    document.styles['Normal'].font.name = u'宋体'
    document.styles['Normal']._element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
    document.styles['Normal'].font.size = Pt(10)
    document.add_paragraph('土地分类面积表')
    table_styles = ['Table Grid']

    section = document.sections[0]
    section.page_width = Cm(21)  # 设置A4纸的宽度
    section.page_height = Cm(29.7)  # 设置A4纸的高度

    # A4长宽互换
    # 设置页面横向
    section.orientation = WD_ORIENTATION.LANDSCAPE
    new_width, new_height = section.page_height, section.page_width
    section.page_width = new_width
    section.page_height = new_height

    # for ws in wb.worksheets:
    ws = wb.worksheets[0]
    rows = list(ws.rows)
    print(rows[0])
    table = document.add_table(rows=len(rows),
                               cols=len(rows[0]),
                               style=choice(table_styles))

    for irow, row in enumerate(rows):
        for icol, col in enumerate(row):
            table.cell(irow, icol).text = str(col.value)
            # None全部转为0
            if table.cell(irow, icol).text == 'None':
                table.cell(irow, icol).text = '0'
    table.cell(1, 21).text = ' '
    table.cell(2, 21).text = ' '

    '''
    整数保留整数，小数保留两位小数
    删除会变成整数保留整数，小数保留三位小数
    也可改成全部保留两位小数
    '''
    print(len(rows[0]))
    for jrow in range(3, len(rows)):
        for jcol in range(1, len(rows[0])):
            # table.cell(len(rows)-2, 1).text = ' '
            if table.cell(jrow, jcol).text != '0':
                if re.findall(r'(?=.{3})\.', (table.cell(jrow, jcol).text)) == ['.']:
                    table.cell(jrow, jcol).text = str("{:.2f}".format(float(table.cell(jrow, jcol).text)))
            else:
                table.cell(jrow, jcol).text = ' '
            # if re.findall(r'(?=.{3})\.',(table.cell(irow, icol).text)) == '.':
            #     table.cell(irow, icol).text = str("{:.2f}".format(int(table.cell(jrow,jcol).text))).text
            #     print(str("{:.2f}".format(int(table.cell(jrow, jcol).text))).text)
            #     print(1)

    table.cell(0, 0).merge(table.cell(2, 0)).text = table.cell(0, 0).text
    table.cell(0, 1).merge(table.cell(0, 10)).text = table.cell(0, 1).text
    table.cell(0, 11).merge(table.cell(0, 19)).text = table.cell(0, 11).text
    table.cell(1, 1).merge(table.cell(1, 3)).text = table.cell(1, 1).text
    table.cell(1, 6).merge(table.cell(1, 10)).text = table.cell(1, 6).text
    table.cell(1, 11).merge(table.cell(1, 12)).text = table.cell(1, 11).text
    table.cell(1, 14).merge(table.cell(1, 16)).text = table.cell(1, 14).text

    table.width = Cm(44)

    for cell in table.columns[9].cells:
        cell.width = Cm(1.85)
    for cell in table.columns[13].cells:
        cell.width = Cm(1.85)
    for cell in table.columns[17].cells:
        cell.width = Cm(1.85)
    for cell in table.columns[18].cells:
        cell.width = Cm(1.85)
    for cell in table.columns[19].cells:
        cell.width = Cm(1.85)
    for cell in table.columns[20].cells:
        cell.width = Cm(1.85)

    for table_row in range(0, len(rows)):
        for table_col in range(0, 22):
            # 垂直居中
            table.cell(table_row, table_col).paragraphs[0].alignment = WD_TABLE_ALIGNMENT.CENTER
            table.cell(table_row, table_col).vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER
    for row in table.rows:
        # 设置行高
        row.height = Cm(0.65)

    # document.save(fn[:-4] + 'docx')
    document.save('report2.docx')


# xlsx2docx2()
xlsx2docx2('test2.xlsx')
